﻿using System;
using System.Text;
using System.Data;
using VIT.DataHelper.DataAccess;
using System.Data.SqlClient;

namespace VIT.Business
{
    public class BLArticle
    {
        SQLSupport sqlSupport = new SQLSupport();

        #region Properties
        public int Id { get; set; }
        public int CategoryId { get; set; }
        public string LanguageId { get; set; }
        public string Image { get; set; }
        public string Title { get; set; }
        public string Brief { get; set; }        
        public string Contents { get; set; }
        public string Tag { get; set; }
        public string Type { get; set; }
        public int Views { get; set; }
        public DateTime DisplayDate { get; set; }
        #endregion

        #region Constructor
        public BLArticle()
        {
            this.Id = -1;
            this.CategoryId = 0;
            this.LanguageId = string.Empty;
            this.Image = string.Empty;
            this.Title = string.Empty;
            this.Brief = string.Empty;            
            this.Contents = string.Empty;
            this.Tag = string.Empty;
            this.Type = string.Empty;
            this.DisplayDate = DateTime.Now;
        }
        #endregion

        #region Methods
        public DataTable GetList(int category)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.Append("select a.id, al.Title,Brief,Image,DisplayDate FROM Article a, Article_Language al where a.Id=ArticleId and al.LanguageID=@LanguageID and CategoryId = @CategoryId and Published=1 order by Orders, DisplayDate desc");
            SqlParameter CategoryId = new SqlParameter("@CategoryId", category);
            SqlParameter LanguageId = new SqlParameter("@LanguageID", this.LanguageId);
            return sqlSupport.ExecuteDataset(false, strQuery.ToString(), LanguageId, CategoryId).Tables[0];
        }

        public DataTable GetList(int top, int category)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.Append("select top " + top + " a.id, al.Title,Brief,Image,DisplayDate FROM Article a, Article_Language al where a.Id=ArticleId and al.LanguageID=@LanguageID and CategoryId = @CategoryId and Published=1 order by Orders, DisplayDate desc");
            SqlParameter CategoryId = new SqlParameter("@CategoryId", category);
            SqlParameter LanguageId = new SqlParameter("@LanguageID", this.LanguageId);
            return sqlSupport.ExecuteDataset(false, strQuery.ToString(), LanguageId, CategoryId).Tables[0];
        }

        public DataTable GetNewsOrther(int top, int id, int category, string type="")
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.Append("select top ");
            strQuery.Append(top);
            strQuery.Append(" a.id, al.Title,Brief,Image,DisplayDate,Type FROM Article a, Article_Language al where a.Id=ArticleId and al.LanguageID=@LanguageID and CategoryId = @CategoryId and a.id<>@Id and Published=1 and Type like '%");
            strQuery.Append(type.Replace(" ", "%"));
            strQuery.Append("%' order by Orders, DisplayDate desc");
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter CategoryId = new SqlParameter("@CategoryId", category);
            SqlParameter LanguageId = new SqlParameter("@LanguageID", this.LanguageId);
            return sqlSupport.ExecuteDataset(false, strQuery.ToString(), LanguageId, CategoryId, Id).Tables[0];
        }

        public void GetSingle()
        {
            if (Exist())
            {
                string strQuery = "select CategoryId, Title, Image, Brief, Contents, Tag, Type, Orders, Published, DisplayDate from Article a left join Article_Language l on Id=ArticleId and LanguageID=@LanguageId where Id=@Id";
                SqlParameter Id = new SqlParameter("@Id", this.Id);
                SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
                DataTable dt = sqlSupport.ExecuteDataset(false, strQuery, Id, LanguageId).Tables[0];

                if (dt.Rows.Count > 0)
                {
                    this.Image = dt.Rows[0]["Image"].ToString();
                    this.Title = dt.Rows[0]["Title"].ToString();
                    this.Brief = dt.Rows[0]["Brief"].ToString();
                    this.Contents = dt.Rows[0]["Contents"].ToString();
                    this.Tag = dt.Rows[0]["Tag"].ToString();
                    this.Type = dt.Rows[0]["Type"].ToString();
                    this.CategoryId = Convert.ToInt32(dt.Rows[0]["CategoryId"]);                   
                    if (!string.IsNullOrEmpty(dt.Rows[0]["DisplayDate"].ToString())) 
                        this.DisplayDate = (DateTime)dt.Rows[0]["DisplayDate"];
                }
            }
            else this.Id = -1;
        }

        private bool Exist()
        {
            string strQuery = "select Id from Article where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", this.Id);
            return sqlSupport.ExecuteScalar(false, strQuery, Id) != null;
        }

        public DataTable Search(string key)
        {
            string strQuery = "select Id, Title, Image, substring(Brief,0,200) as Brief, Orders, Tag, Published from Article, Article_Language l"
                                + " where Id=ArticleId and LanguageID=@LanguageID and (Title like @vKey or Tag like @vKey) and Published=1 order by Orders, l.DisplayDate DESC";
            SqlParameter LanguageId = new SqlParameter("@LanguageID", this.LanguageId);
            SqlParameter vKey = new SqlParameter("@vKey", "%" + key.Replace(" ", "%") + "%");
            return sqlSupport.ExecuteDataset(false, strQuery, LanguageId, vKey).Tables[0];
        }
        #endregion

    }
}
